package com.hongdee.scm.warningreport.service.impl;

import com.hongdee.common.util.TokenUtil;
import com.hongdee.scm.warningreport.dao.ITScmWarningReportDao;
import com.hongdee.scm.warningreport.service.ITScmWarningReportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 名  称:TScmWarningReportServiceImpl
 * 类  型:class
 * 描  述:
 * 路  径:com.hongdee.scm.warningreport.service.impl
 * 作  者: 术洪超
 * 日  期:2017-06-22 16:11
 */
@Service
public class TScmWarningReportServiceImpl implements ITScmWarningReportService {

    @Autowired
    private ITScmWarningReportDao itScmWarningReportDao;

    /**
     * 重写预警图表（徐钦勇）
     * @param data
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map warningReportNew(Map data, String uid) throws Exception {
        //查询必须选择品牌和预警类型
        if (data.get("brand") == null || StringUtils.isEmpty(data.get("brand"))) {
            throw new Exception("请选择品牌！");
        }
        if (data.get("warningType") == null || StringUtils.isEmpty(data.get("warningType"))) {
            throw new Exception("请选择预警类型！");
        }
        //基础查询baseSql
        StringBuffer baseSql = new StringBuffer();
        List params = new ArrayList();
        baseSql.append("select count(1) as value ");
        //预警warnSql
        StringBuffer daysWarnSql = new StringBuffer();
        StringBuffer comWarnSql = new StringBuffer();
        comWarnSql.append("  (select ws.warning_num as wn from t_scm_warning_setup ws where ws.warning_type = ? " +
                " and ws.level_type =? and ws.level_id=? limit 1 )) ");
        //初始化预警设置查询
        String levelType = "0";
        String levelId = String.valueOf(data.get("brand"));
        //初始化分组设置
        String group = " GROUP BY tss.car_series ";
        //预警筛选（0金融类预警1库存量预警3库存天数预警）
        if(data.get("warningType")!=null && !StringUtils.isEmpty(data.get("warningType"))){
            switch (String.valueOf(data.get("warningType"))){
                //金融预警
                case "0":{
                    daysWarnSql.append(" (TIMESTAMPDIFF(DAY,tss.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) " );
                }
                break;
                //库存量预警
                case "1":{
                    daysWarnSql.append("  (select COUNT(1) from t_scm_stock st where st.brand = ? ");
                    params.add(data.get("brand"));
                }
                break;
                //库存天数预警和金融类预警
                case "3":{
                    daysWarnSql.append(" (TIMESTAMPDIFF(DAY,tss.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) " );
                }
                break;
                default:break;
            }
        }
        //初始化名称
        String name = " ,tsc.car_series name ";
        //左链接leftSql
        StringBuffer leftSql = new StringBuffer();
        //车系转化
        leftSql.append(" left join t_scm_car tsc on tsc.vin = tss.vin ");
        //选了车系查以下车型
        if(data.get("carSeries")!=null&&!StringUtils.isEmpty(data.get("carSeries"))){
            name = " ,tsc.car_model name ";
            //leftSql.append(" left join t_bc_carmodel mn on mn.id = tss.car_model ");
            //预警sql
            if(String.valueOf(data.get("warningType")).equals("1")){
                daysWarnSql.append(" and st.car_series = ? ");
                params.add(data.get("carSeries"));
            }
            levelType = "1";
            levelId = String.valueOf(data.get("carSeries"));
            group = " GROUP BY tsc.car_model ";
        }
        //选了车型查以下配置
        if(data.get("carModel")!=null&&!StringUtils.isEmpty(data.get("carModel"))){
            name = " ,tsc.configure name ";
            //leftSql.append(" left join t_bc_carconfigure cn on cn.id = tss.configure_name ");
            //预警sql
            if(String.valueOf(data.get("warningType")).equals("1")) {
                daysWarnSql.append(" and st.car_model = ? ");
                params.add(data.get("carModel"));
            }
            levelType = "2";
            levelId = String.valueOf(data.get("carModel"));
            group = " GROUP BY tsc.configure ";
        }
        //主表
        baseSql.append(name);
        baseSql.append(" from t_scm_stock tss ");
        StringBuffer totalSql = new StringBuffer();
        totalSql.append(baseSql.toString()+leftSql.toString()+" where "+daysWarnSql.toString()+" > "+comWarnSql.toString());
        params.add(data.get("warningType"));
        params.add(levelType);
        params.add(levelId);
        //仓库
        if (data.get("warehouseId") != null && !StringUtils.isEmpty(data.get("warehouseId"))) {
            totalSql.append(" and tss.warehouse_id = ?");
            params.add(data.get("warehouseId"));
        }
        //公司
        if (data.get("companyId") != null && !data.get("companyId").equals("")) {

            // System.out.println(data.get("companyId"));
            List<String> companyIdList = new ArrayList();
            companyIdList = (List<String>) data.get("companyId");
            if (companyIdList.size()> 0) {
                totalSql.append(" and tss.company_id in ( ");
                for (String companyId : companyIdList) {
                    totalSql.append(" ? , ");
                    params.add(companyId);
                }
                totalSql.append(" '' ) ");
            }
        }
        //预警天数起止
        if(data.get("starts") != null && !StringUtils.isEmpty(data.get("starts"))){
            totalSql.append(" and ("+daysWarnSql+" - "+comWarnSql +" > ? ) ");
            params.add(data.get("warningType"));
            params.add(levelType);
            params.add(levelId);
            params.add(data.get("starts"));
        }
        if(data.get("stops") != null && !StringUtils.isEmpty(data.get("stops"))){
            totalSql.append(" and ("+daysWarnSql+" - "+comWarnSql +" < ? ) ");
            params.add(data.get("warningType"));
            params.add(levelType);
            params.add(levelId);
            params.add(data.get("stops"));
        }
        List list = this.itScmWarningReportDao.list(totalSql.toString()+group,params.toArray());
        Map dataList = new HashMap();
        dataList.put("dataList", list);
        return dataList;
    }
    @Override
    public Map warningReport(Map data, String uid) throws Exception
    {

        if (data.get("brand") == null || StringUtils.isEmpty(data.get("brand"))) {
            throw new Exception("请选择品牌！");
        }
        if (data.get("warningType") == null || StringUtils.isEmpty(data.get("warningType"))) {
            throw new Exception("请选择预警类型！");
        }
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select count(1) as value");

        //选择了品牌
        if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") == null || StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") == null || StringUtils.isEmpty(data.get("carModel")))) {
            sql.append(",TRIM(cc.car_series) as name");
        }
        //选择了车系
        if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") != null && !StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") == null || StringUtils.isEmpty(data.get("carModel")))) {
            sql.append(",trim(cc.car_model) as name");
        }
        //选择了车型
        if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") != null && !StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") != null && !StringUtils.isEmpty(data.get("carModel")))) {
            sql.append(",trim(cc.configure) as name");
        }

        if ("1".equals(data.get("warningType")))  ///库存量预警
        {
            sql.append(" from view_scm_warning_amount cc where");
        }
        else{
            sql.append(" from view_scm_warning_period cc where ");
            //预警类型
           if (data.get("warningType") != null && !StringUtils.isEmpty(data.get("warningType"))) {
            sql.append("  warning_type = ? and ");
            params.add(data.get("warningType"));
            }

            //超预警天数参数1
            if (data.get("starts") != null && !StringUtils.isEmpty(data.get("starts"))) {
                sql.append("  WD >= ? and ");
                params.add(data.get("starts"));
            }
            //超预警天数参数1
            if (data.get("stops") != null && !StringUtils.isEmpty(data.get("stops"))) {
                sql.append("  WD <= ? and ");
                params.add(data.get("stops"));
            }
        }

        if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand")))) {
            sql.append(" cc.brand= ? ");
            params.add(data.get("brand"));
        }
        //选择了车系
        if ( (data.get("carSeries") != null && !StringUtils.isEmpty(data.get("carSeries"))) ) {
            sql.append(" and car_series_id=?");
            params.add(data.get("carSeries"));

        }
        //选择了车型
        if ( (data.get("carModel") != null && !StringUtils.isEmpty(data.get("carModel")))) {
            sql.append(" and car_model_id = ?");
            params.add(data.get("carModel"));
        }
        //公司
        if (data.get("companyId") != null && !StringUtils.isEmpty(data.get("companyId"))) {

            List<String> companyIdList = new ArrayList();
            if("8a1029015ac58d43015ac65d411e000d".equals(((List<String>)data.get("companyId")).get(0))){//集团级
                List<Map<String,Object>> comList = TokenUtil.getDataCompany(uid);
                if(comList.size()>0){
                    for (Map<String,Object> com:comList){
                        companyIdList.add(com.get("id").toString());
                    }
                }else{
                    throw new Exception("数据权限有误！");
                }
            }else{
                companyIdList = (List<String>) data.get("companyId");
            }
            if (data.get("companyId").toString().length() > 0) {
                sql.append(" and company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");
            }
        }

        //仓库
        if (data.get("warehouseId")!=null&&!"".equals(data.get("warehouseId"))) {//仓库
            List<String> warehouseList = (ArrayList) data.get("warehouseId");

            if (data.get("warehouseId").toString().length() > 0) {
                sql.append(" and exists (select id from t_scm_stock s where s.car_id=cc.cid and s.warehouse_id in (  ");
                for (String warehouseId : warehouseList) {
                    sql.append(" ? , ");
                    params.add(warehouseId);
                }
                sql.append(" '' ) )");
            }
        }

        if ("1".equals(data.get("warningType"))) {  ///库存量预警

            //选择了品牌
            if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") == null || StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") == null || StringUtils.isEmpty(data.get("carModel")))) {
                sql.append(" and EXISTS ( select p.label from ( select label,value from (")
                   .append(" select DISTINCT t.label as label,mySeriesCount(t.label) as value from (")
                   .append(" select tbcs.id label,tswc.count_date cdate")
                   .append(" from t_scm_warning_count tswc")
                   .append(" left join t_scm_warning_setup tsws on tswc.setup_id = tsws.id")
                   .append(" left join t_bc_car_series tbcs on tsws.level_id = tbcs.id")
                   .append(" left join t_bc_brand tbb on tbb.b_code = tbcs.brand_code where tbb.b_code = ?");
                params.add(data.get("brand"));
                sql.append(" ) t ) d ");
                //超预警天数参数1
                boolean flag = false;
                if (data.get("starts") != null && !StringUtils.isEmpty(data.get("starts"))) {
                    sql.append(" where d.value >= ? and ");
                    params.add(data.get("starts"));
                    flag = true;
                }
                //超预警天数参数1
                if (data.get("stops") != null && !StringUtils.isEmpty(data.get("stops"))) {
                    if(flag){
                        sql.append("  d.value <= ? ");
                    }else{
                        sql.append(" where d. <= ? ");
                    }

                    params.add(data.get("stops"));
                }
                sql.append(" ) p where p.label = cc.car_series_id ) ");
            }
            //选择了车系
            if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") != null && !StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") == null || StringUtils.isEmpty(data.get("carModel")))) {
                sql.append(" and EXISTS ( select p.label from ( select label,value from (")
                   .append(" select DISTINCT t.label as label,myModelCount(t.label) as value from (")
                   .append(" select tbc.id label,tswc.count_date cdate from t_scm_warning_count tswc")
                   .append(" left join t_scm_warning_setup tsws on tswc.setup_id = tsws.id")
                   .append(" left join t_bc_carmodel tbc on tsws.level_id = tbc.id")
                   .append(" left join t_bc_car_series tbcs on tbc.series_id = tbcs.id")
                   .append(" left join t_bc_brand tbb on tbb.b_code = tbc.brand_code")
                   .append(" where tbb.b_code = ? and tbcs.id = ? ");
                params.add(data.get("brand"));
                params.add(data.get("carSeries"));
                sql.append(" ) t ) d");
                //超预警天数参数1
                boolean flag = false;
                if (data.get("starts") != null && !StringUtils.isEmpty(data.get("starts"))) {
                    sql.append(" where d.value >= ? and ");
                    params.add(data.get("starts"));
                    flag = true;
                }
                //超预警天数参数1
                if (data.get("stops") != null && !StringUtils.isEmpty(data.get("stops"))) {
                    if(flag){
                        sql.append("  d.value <= ? ");
                    }else{
                        sql.append(" where d. <= ? ");
                    }

                    params.add(data.get("stops"));
                }
                sql.append(" ) p where p.label = cc.car_model_id )");

            }
            //选择了车型
            if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") != null && !StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") != null && !StringUtils.isEmpty(data.get("carModel")))) {
                sql.append(" and EXISTS( select p.label from ( select label,value from (")
                   .append(" select DISTINCT t.label as label,myConfigureCount(t.label,t.model_id) as value from (")
                   .append(" select tbcc.model_id,tbcc.configure_name label,tswc.count_date cdate")
                   .append(" from t_scm_warning_count tswc")
                   .append(" left join t_scm_warning_setup tsws on tswc.setup_id = tsws.id")
                   .append(" left join t_bc_carmodel tbc on tsws.level_id = tbc.id")
                   .append(" left join ( select c.car_model_id model_id,c.configure configure_name")
                   .append(" from t_scm_stock s left join t_scm_car c on c.vin = s.vin")
                   .append(" where c.car_model_id = ? ");
                params.add(data.get("carModel"));
                sql.append(" group by c.configure_id ) tbcc on tbc.id = tbcc.model_id")
                   .append(" left join t_bc_car_series tbcs on tbc.series_id = tbcs.id")
                   .append(" left join t_bc_brand tbb on tbb.b_code = tbc.brand_code")
                   .append(" where tbb.b_code = ? and tbcs.id = ? and tbc.id =? ");
                params.add(data.get("brand"));
                params.add(data.get("carSeries"));
                params.add(data.get("carModel"));
                sql.append(" ) t ) d");
                //超预警天数参数1
                boolean flag = false;
                if (data.get("starts") != null && !StringUtils.isEmpty(data.get("starts"))) {
                    sql.append(" where d.value >= ? and ");
                    params.add(data.get("starts"));
                    flag = true;
                }
                //超预警天数参数1
                if (data.get("stops") != null && !StringUtils.isEmpty(data.get("stops"))) {
                    if(flag){
                        sql.append("  d.value <= ? ");
                    }else{
                        sql.append(" where d. <= ? ");
                    }

                    params.add(data.get("stops"));
                }
                sql.append(" ) p where p.label = cc.configure )");
           }

        }
        //选择了品牌
        if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") == null || StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") == null || StringUtils.isEmpty(data.get("carModel")))) {
            sql.append(" group by car_series ");
        }
        //选择了车系
        if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") != null && !StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") == null || StringUtils.isEmpty(data.get("carModel")))) {
            sql.append(" group by car_model ");
        }
        //选择了车型
        if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") != null && !StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") != null && !StringUtils.isEmpty(data.get("carModel")))) {
            sql.append(" group by configure ");
        }

        List list = this.itScmWarningReportDao.list(sql.toString(), params.toArray());
        Map dataList = new HashMap();
        dataList.put("dataList", list);
        return dataList;
    }

    @Override
    public Map warningPage(String start, String limit, String name, String uid,String type,Map data) throws Exception {
        if (data.get("brand") == null || StringUtils.isEmpty(data.get("brand"))) {
            throw new Exception("请选择品牌！");
        }
        if (data.get("warningType") == null || StringUtils.isEmpty(data.get("warningType"))) {
            throw new Exception("请选择预警类型！");
        }
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select * from (select cc.*, datediff(curdate(), date(enter_warehouse_date_start)) stockDay, enter_warehouse_date_start enterDate ");

        if ("1".equals(data.get("warningType")))  ///库存量预警
        {
            sql.append(" from view_scm_warning_amount cc where ");
        }
        else{
            sql.append(" from view_scm_warning_period cc where ");
            //预警类型
            if (data.get("warningType") != null && !StringUtils.isEmpty(data.get("warningType"))) {
                sql.append("  warning_type = ? and ");
                params.add(data.get("warningType"));
            }

            //超预警天数参数1
            if (data.get("starts") != null && !StringUtils.isEmpty(data.get("starts"))) {
                sql.append("  WD >= ? and ");
                params.add(data.get("starts"));
            }
            //超预警天数参数1
            if (data.get("stops") != null && !StringUtils.isEmpty(data.get("stops"))) {
                sql.append("  WD <= ? and ");
                params.add(data.get("stops"));
            }
        }
        //选择了品牌
        if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand")))) {
            sql.append(" cc.brand= ? ");
            params.add(data.get("brand"));
        }
        //选择了车系
        if ( (data.get("carSeries") != null && !StringUtils.isEmpty(data.get("carSeries"))) ) {
            sql.append(" and car_series_id=?");
            params.add(data.get("carSeries"));

        }
        //选择了车型
        if ( (data.get("carModel") != null && !StringUtils.isEmpty(data.get("carModel")))) {
            sql.append(" and car_model_id = ?");
            params.add(data.get("carModel"));
        }
        //公司
        if (data.get("companyId") != null && !StringUtils.isEmpty(data.get("companyId"))) {

            List<String> companyIdList = new ArrayList();
            if("8a1029015ac58d43015ac65d411e000d".equals(((List<String>)data.get("companyId")).get(0))){//集团级
                List<Map<String,Object>> comList = TokenUtil.getDataCompany(uid);
                if(comList.size()>0){
                    for (Map<String,Object> com:comList){
                        companyIdList.add(com.get("id").toString());
                    }
                }else{
                    throw new Exception("数据权限有误！");
                }
            }else{
                companyIdList = (List<String>) data.get("companyId");
            }
            if (data.get("companyId").toString().length() > 0) {
                sql.append(" and company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");
            }
        }

        //仓库
        if (data.get("warehouseId")!=null&&!"".equals(data.get("warehouseId"))) {//仓库
            List<String> warehouseList = (ArrayList) data.get("warehouseId");

            if (data.get("warehouseId").toString().length() > 0) {
                sql.append(" and exists (select id from t_scm_stock s where s.car_id=cc.cid and s.warehouse_id in (  ");
                for (String warehouseId : warehouseList) {
                    sql.append(" ? , ");
                    params.add(warehouseId);
                }
                sql.append(" '' ) )");
            }
        }

        if ("1".equals(data.get("warningType"))) {  ///库存量预警

            //选择了品牌
            if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") == null || StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") == null || StringUtils.isEmpty(data.get("carModel")))) {
                sql.append(" and EXISTS ( select p.label from ( select label,value from (")
                        .append(" select DISTINCT t.label as label,mySeriesCount(t.label) as value from (")
                        .append(" select tbcs.id label,tswc.count_date cdate")
                        .append(" from t_scm_warning_count tswc")
                        .append(" left join t_scm_warning_setup tsws on tswc.setup_id = tsws.id")
                        .append(" left join t_bc_car_series tbcs on tsws.level_id = tbcs.id")
                        .append(" left join t_bc_brand tbb on tbb.b_code = tbcs.brand_code where tbb.b_code = ?");
                params.add(data.get("brand"));
                sql.append(" ) t ) d ");
                //超预警天数参数1
                boolean flag = false;
                if (data.get("starts") != null && !StringUtils.isEmpty(data.get("starts"))) {
                    sql.append(" where d.value >= ? and ");
                    params.add(data.get("starts"));
                    flag = true;
                }
                //超预警天数参数1
                if (data.get("stops") != null && !StringUtils.isEmpty(data.get("stops"))) {
                    if(flag){
                        sql.append("  d.value <= ? ");
                    }else{
                        sql.append(" where d. <= ? ");
                    }

                    params.add(data.get("stops"));
                }
                sql.append(" ) p where p.label = cc.car_series_id ) ");
            }
            //选择了车系
            if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") != null && !StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") == null || StringUtils.isEmpty(data.get("carModel")))) {
                sql.append(" and EXISTS ( select p.label from ( select label,value from (")
                        .append(" select DISTINCT t.label as label,myModelCount(t.label) as value from (")
                        .append(" select tbc.id label,tswc.count_date cdate from t_scm_warning_count tswc")
                        .append(" left join t_scm_warning_setup tsws on tswc.setup_id = tsws.id")
                        .append(" left join t_bc_carmodel tbc on tsws.level_id = tbc.id")
                        .append(" left join t_bc_car_series tbcs on tbc.series_id = tbcs.id")
                        .append(" left join t_bc_brand tbb on tbb.b_code = tbc.brand_code")
                        .append(" where tbb.b_code = ? and tbcs.id = ? ");
                params.add(data.get("brand"));
                params.add(data.get("carSeries"));
                sql.append(" ) t ) d");
                //超预警天数参数1
                boolean flag = false;
                if (data.get("starts") != null && !StringUtils.isEmpty(data.get("starts"))) {
                    sql.append(" where d.value >= ? and ");
                    params.add(data.get("starts"));
                    flag = true;
                }
                //超预警天数参数1
                if (data.get("stops") != null && !StringUtils.isEmpty(data.get("stops"))) {
                    if(flag){
                        sql.append("  d.value <= ? ");
                    }else{
                        sql.append(" where d. <= ? ");
                    }

                    params.add(data.get("stops"));
                }
                sql.append(" ) p where p.label = cc.car_model_id ) ");

            }
            //选择了车型
            if ((data.get("brand") != null && !StringUtils.isEmpty(data.get("brand"))) && (data.get("carSeries") != null && !StringUtils.isEmpty(data.get("carSeries"))) && (data.get("carModel") != null && !StringUtils.isEmpty(data.get("carModel")))) {
                sql.append(" and EXISTS( select p.label from ( select label,value from (")
                        .append(" select DISTINCT t.label as label,myConfigureCount(t.label,t.model_id) as value from (")
                        .append(" select tbcc.model_id,tbcc.configure_name label,tswc.count_date cdate")
                        .append(" from t_scm_warning_count tswc")
                        .append(" left join t_scm_warning_setup tsws on tswc.setup_id = tsws.id")
                        .append(" left join t_bc_carmodel tbc on tsws.level_id = tbc.id")
                        .append(" left join ( select c.car_model_id model_id,c.configure configure_name")
                        .append(" from t_scm_stock s left join t_scm_car c on c.vin = s.vin")
                        .append(" where c.car_model_id = ? ");
                params.add(data.get("carModel"));
                sql.append(" group by c.configure_id ) tbcc on tbc.id = tbcc.model_id")
                        .append(" left join t_bc_car_series tbcs on tbc.series_id = tbcs.id")
                        .append(" left join t_bc_brand tbb on tbb.b_code = tbc.brand_code")
                        .append(" where tbb.b_code = ? and tbcs.id = ? and tbc.id = ? ");
                params.add(data.get("brand"));
                params.add(data.get("carSeries"));
                params.add(data.get("carModel"));
                sql.append(" ) t ) d");
                //超预警天数参数1
                boolean flag = false;
                if (data.get("starts") != null && !StringUtils.isEmpty(data.get("starts"))) {
                    sql.append(" where d.value >= ? and ");
                    params.add(data.get("starts"));
                    flag = true;
                }
                //超预警天数参数1
                if (data.get("stops") != null && !StringUtils.isEmpty(data.get("stops"))) {
                    if(flag){
                        sql.append("  d.value <= ? ");
                    }else{
                        sql.append(" where d. <= ? ");
                    }

                    params.add(data.get("stops"));
                }
                sql.append(" ) p where p.label = cc.configure ) ");
            }

        }
        if(name!=null&&!name.equals("")){
            if(data.get("carSeries")!=null&&!data.get("carSeries").equals("")&&(data.get("carModel")==null||data.get("carModel").equals(""))){
                sql.append(" and cc.car_model =?");
                params.add(name);
            }
            if(data.get("carModel")!=null&&!data.get("carModel").equals("")){
                sql.append(" and cc.configure =?");
                params.add(name);
            }
            if((data.get("carSeries")==null||data.get("carSeries").equals(""))&&(data.get("carModel")==null||data.get("carModel").equals(""))){
                sql.append(" and cc.car_series =?");
                params.add(name);
            }
        }

        sql.append(" ) as temp ");
        return this.itScmWarningReportDao.page(sql.toString(), params.toArray(), start, limit);

    }
}
